﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_NoFinish]
	(
		@CompanyId int,
		@DepId int,
		@StateId int,
		@TypeId int,
		@WayId int,
		@ProcessId int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@NOE_Flag int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY TradeDate DESC,Id DESC)AS Row
		,Id
		,OId
		,isnull(OrderId,0) as OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,isnull(YingShouPrice,0) as YingShouPrice
		,isnull(ShiShouPrice,0) as ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId
		,IsGreen
		,NOE_Flag
		,IsNULL((Select Title From Order_State Where Id=[Order].StateId),'...') As StateName
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
		From [Order]
		Where (@CompanyId=0 Or CompanyId=@CompanyId) And (@DepId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
		And CreateDate>=@sRq And CreateDate<=@eRq
		And ((@StateId=0 And StateId<5) Or StateId=@StateId) And (@TypeId=0 Or TypeId=@TypeId)
		And (@WayId=0 Or WayId=@WayId) And (@ProcessId=0 Or ProcessId=@ProcessId)
		And ((@NOE_Flag=100 And NOE_Flag<>1) Or NOE_Flag=@NOE_Flag)
		And (@Stext='' Or CusName like '%'+@Stext+'%' Or Salesman like '%'+@Stext+'%')
	)

	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex Order By row
End
